package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Glossario;
import model.Login;
import model.Pais;
import model.Termo;

public class TermosDaoImpl implements TermosDao {

	Connection c;
	
	public TermosDaoImpl(){
		GenericDao gDao = new GenericDao();
		c = gDao.getConnection();
	}
	
	@Override
	public String cadastraTermo(Termo t) throws SQLException {
		String sql = "{call sp_cat (?,?,?,?,?,?,?)";
		CallableStatement cs = c.prepareCall(sql);
		cs.setString(1, "I");
		cs.setNull(2, java.sql.Types.INTEGER);
		cs.setString(3, t.getRa());
		cs.setInt(4, t.getIdPais());
		cs.setInt(5, t.getIdCategoria());
		cs.setString(6, t.getTermo());
		cs.registerOutParameter(7, java.sql.Types.VARCHAR);
		cs.execute();
		cs.close();
		return cs.getString(6);
	}

	@Override
	public String editaTermo(Termo t) throws SQLException {
		String sql = "{call sp_cat (?,?,?,?,?,?,?)";
		CallableStatement cs = c.prepareCall(sql);
		cs.setString(1, "U");
		cs.setInt(2, t.getId());
		cs.setString(3, t.getRa());
		cs.setInt(4, t.getIdPais());
		cs.setInt(5, t.getIdCategoria());
		cs.setString(6, t.getTermo());
		cs.registerOutParameter(7, java.sql.Types.VARCHAR);
		cs.execute();
		cs.close();
		return cs.getString(6);
	}

	@Override
	public String excluiTermo(Termo t) throws SQLException {
		String sql = "{call sp_cat (?,?,?,?,?,?,?)";
		CallableStatement cs = c.prepareCall(sql);
		cs.setString(1, "D");
		cs.setInt(2, t.getId());
		cs.setNull(3, java.sql.Types.VARCHAR);
		cs.setNull(4, java.sql.Types.INTEGER);
		cs.setNull(5, java.sql.Types.INTEGER);
		cs.setNull(6, java.sql.Types.VARCHAR);
		cs.registerOutParameter(7, java.sql.Types.VARCHAR);
		cs.execute();
		cs.close();
		return cs.getString(7);
	}

	@Override
	public List<Glossario> listaTermos(Pais p, Login l, String letra)
			throws SQLException {
		List<Glossario> lista = new ArrayList<Glossario>();
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT glossario.id_termo, glossario.ra_aluno, glossario.id_pais, ");
		sb.append("pais.nome, categorias.id, categorias.tipo, glossario.termo ");
		sb.append("FROM glossario INNER JOIN pais ");
		sb.append("ON glossario.id_pais = pais.id ");
		sb.append("INNER JOIN categorias ");
		sb.append("ON glossario.id_categoria = categorias.id ");
		if (p != null || l != null || letra != null){
			sb.append("WHERE ");
			if (p != null || letra != null){
				sb.append("pais.nome LIKE ?");
			} else {
				if (l != null){
					sb.append("gloassrio.ra_aluno = ?");
				}
			}
		}
		PreparedStatement ps = c.prepareStatement(sb.toString());
		if (p != null || l != null || letra != null){
			if (p != null){
				ps.setString(1, p.getNome());
			} else {
				if (l != null){
					ps.setString(1, l.getSenha());
				} else {
					if (letra != null){
						ps.setString(1, letra);
					}	
				}
			}
		}
		ResultSet rs = ps.executeQuery();
		while (rs.next()){
			Glossario g = new Glossario();
			g.setIdTermo(rs.getInt("id_termo"));
			g.setRa(rs.getString("ra_aluno"));
			g.setIdPais(rs.getInt("id_pais"));
			g.setNomePais(rs.getString("nome"));
			g.setIdCategoria(rs.getInt("id_categoria"));
			g.setTipoCategoria(rs.getString("tipo"));
			g.setTermo(rs.getString("termo"));
			lista.add(g);
		}
		return lista;
	}

}
